﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using cinemawcftest.DBLayer;

public class DBTicket : DataBase
{

    DBShowTime dbst = new DBShowTime();
    DBSeat dbseat = new DBSeat();
    public List<Ticket> Read(Ticket tick)
    {


        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket WHERE Price = @Price AND SeatID = @Seat AND ShowTime = @ShowTime AND BookingID = @Booking", Connection);
            cmd.Parameters.Add("@Price", SqlDbType.Float).Value = tick.TicketPrice;
            cmd.Parameters.Add("@Seat", SqlDbType.Int).Value = tick.Seat;
            cmd.Parameters.Add("@ShowTime", SqlDbType.Int).Value = dbst.GetID(tick.ShowTime);
            cmd.Parameters.Add("@Booking", SqlDbType.Int).Value = tick.Booking.Id;
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["Price"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"])));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }
    public List<Ticket> ReadAll()
    {
        

        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket", Connection);
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["Price"],dbst.Read((int)rdr["ShowTimeID"]),dbseat.Read((int)rdr["SeatID"])));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }


    public List<Ticket> Read(Booking booking)
    {
        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket INNER JOIN Booking ON Ticket.BookingID=Booking.ID WHERE CustomerPhone = @Phone", Connection);
            cmd.Parameters.Add("@Phone", SqlDbType.VarChar, 255).Value = booking.CustomerPhone;

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["Price"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]), booking));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }

    public List<Ticket> ReadAll(int id)
    {


        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket WHERE Id = @ID", Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["Price"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"])));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }

    public List<Ticket> ReadFromBookingId(int id)
    {
        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        DBBooking dbb = new DBBooking();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket INNER JOIN Booking ON Ticket.BookingID=Booking.ID WHERE Booking.ID = @Id", Connection);
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["Price"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]), dbb.read(id)));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }

    public int Insert(Ticket tick)
    {
        
        int wentWell = 0;
        string query =
            "INSERT INTO Ticket (TicketPrice, SeatID, ShowTimeID, BookingID) VALUES (@Price, @Seat, @ShowTime, @Booking)";
        //Don't insert something that already exists!
        if (this.Read(tick).Count < 1)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(query, Connection);
                cmd.Parameters.Add("@Price", SqlDbType.Float).Value = tick.TicketPrice;
                cmd.Parameters.Add("@Seat", SqlDbType.Int).Value = tick.Seat;
                cmd.Parameters.Add("@ShowTime", SqlDbType.Int).Value = dbst.GetID(tick.ShowTime);
                cmd.Parameters.Add("@Booking", SqlDbType.Int).Value = tick.Booking.Id;

                Connection.Open();
                wentWell = cmd.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                wentWell = -1;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Connection.Close();
            }
        }
        return wentWell;
    }

}

